package com.chen.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ExcelReader {

    public List<WaybillData> readWaybillData(String filePath) throws IOException {
        List<WaybillData> waybillDataList = new ArrayList<>();
        
        try (InputStream inputStream = new FileInputStream(filePath)) {
            Workbook workbook = createWorkbook(filePath, inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            
            Map<String, Integer> columnIndexMap = parseHeader(sheet);
            
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    WaybillData waybillData = parseRowData(row, columnIndexMap);
                    if (waybillData != null) {
                        waybillDataList.add(waybillData);
                    }
                }
            }
            
            workbook.close();
        }
        
        return waybillDataList;
    }

    private Workbook createWorkbook(String filePath, InputStream inputStream) throws IOException {
        if (filePath.endsWith(".xlsx")) {
            return new XSSFWorkbook(inputStream);
        } else if (filePath.endsWith(".xls")) {
            return new HSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("不支持的文件格式，请使用.xls或.xlsx文件");
        }
    }

    private Map<String, Integer> parseHeader(Sheet sheet) {
        Map<String, Integer> columnIndexMap = new HashMap<>();
        Row headerRow = sheet.getRow(0);
        
        if (headerRow != null) {
            for (Cell cell : headerRow) {
                String cellValue = getCellValue(cell).trim();
                
                if (cellValue.contains("运单号") || cellValue.contains("运单")) {
                    columnIndexMap.put("waybillNo", cell.getColumnIndex());
                } else if (cellValue.contains("司机") && cellValue.contains("姓名")) {
                    columnIndexMap.put("driverName", cell.getColumnIndex());
                } else if (cellValue.contains("车牌号") || cellValue.contains("车牌")) {
                    columnIndexMap.put("plateNumber", cell.getColumnIndex());
                }
            }
        }
        
        return columnIndexMap;
    }

    private WaybillData parseRowData(Row row, Map<String, Integer> columnIndexMap) {
        if (!columnIndexMap.containsKey("waybillNo") || 
            !columnIndexMap.containsKey("driverName") || 
            !columnIndexMap.containsKey("plateNumber")) {
            return null;
        }
        
        String waybillNo = getCellValue(row.getCell(columnIndexMap.get("waybillNo")));
        String driverName = getCellValue(row.getCell(columnIndexMap.get("driverName")));
        String plateNumber = getCellValue(row.getCell(columnIndexMap.get("plateNumber")));
        
        if (waybillNo.isEmpty() && driverName.isEmpty() && plateNumber.isEmpty()) {
            return null;
        }
        
        return new WaybillData(waybillNo, driverName, plateNumber);
    }

    private String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}